package br.com.egg.finances.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import br.com.egg.finances.business.dao.util.BDUtils;
import br.com.egg.finances.business.exception.BusinessErrorException;
import br.com.egg.finances.model.CentroCusto;

public class CentroCustoDAO extends DefaultDAO<CentroCusto> {

	@Override
	public void insert(CentroCusto entity) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			StringBuilder sbInsert = new StringBuilder();
			sbInsert.append("INSERT INTO DEGGCENTROCUSTO ");
			sbInsert.append("VALUES (?, ?, ?) ");

			String insert = sbInsert.toString();
			System.out.println(insert);

			connection = BDUtils.getConnection();
			preparedStatement = connection.prepareStatement(insert);
			preparedStatement.setString(1, entity.getCdUsuario());
			preparedStatement.setInt(2, entity.getCdCentroCusto());
			preparedStatement.setString(3, entity.getNmCentroCusto());

			preparedStatement.executeUpdate();
		} catch (Exception e) {
			throw new BusinessErrorException(e);
		} finally {
			BDUtils.closeResources(connection, preparedStatement, null);
		}
	}

	@Override
	public void update(CentroCusto entity) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			StringBuilder sbUpdate = new StringBuilder();
			sbUpdate.append("UPDATE DEGGCENTROCUSTO ");
			sbUpdate.append("SET NMCENTROCUSTO = ? ");
			sbUpdate.append("WHERE CDUSUARIO = ? AND CDCENTROCUSTO = ? ");

			String update = sbUpdate.toString();
			System.out.println(update);

			connection = BDUtils.getConnection();
			preparedStatement = connection.prepareStatement(update);
			preparedStatement.setString(1, entity.getNmCentroCusto());
			preparedStatement.setString(2, entity.getCdUsuario());
			preparedStatement.setInt(3, entity.getCdCentroCusto());

			preparedStatement.executeUpdate();
		} catch (Exception e) {
			throw new BusinessErrorException(e);
		} finally {
			BDUtils.closeResources(connection, preparedStatement, null);
		}
	}

	@Override
	public void delete(CentroCusto entity) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			StringBuilder sbUpdate = new StringBuilder();
			sbUpdate.append("DELETE FROM DEGGCENTROCUSTO ");
			sbUpdate.append("WHERE CDUSUARIO = ? AND CDCENTROCUSTO = ? ");

			String update = sbUpdate.toString();
			System.out.println(update);

			connection = BDUtils.getConnection();
			preparedStatement = connection.prepareStatement(update);
			preparedStatement.setString(1, entity.getCdUsuario());
			preparedStatement.setInt(2, entity.getCdCentroCusto());

			preparedStatement.executeUpdate();
		} catch (Exception e) {
			throw new BusinessErrorException(e);
		} finally {
			BDUtils.closeResources(connection, preparedStatement, null);
		}
	}

	@Override
	public CentroCusto find(CentroCusto entityFilter) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			StringBuilder sbUpdate = new StringBuilder();
			sbUpdate.append("SELECT CC.CDUSUARIO AS CDUSUARIO, CC.CDCENTROCUSTO AS CODIGO, CC.NMCENTROCUSTO AS NOME FROM DEGGCENTROCUSTO CC ");
			sbUpdate.append("WHERE CDUSUARIO = ? AND CDCENTROCUSTO = ? ");

			String update = sbUpdate.toString();
			System.out.println(update);

			connection = BDUtils.getConnection();
			preparedStatement = connection.prepareStatement(update);
			preparedStatement.setString(1, entityFilter.getCdUsuario());
			preparedStatement.setInt(2, entityFilter.getCdCentroCusto());

			CentroCusto centroCusto = null;
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				centroCusto = new CentroCusto();
				centroCusto.setCdUsuario(resultSet.getString("CDUSUARIO"));
				centroCusto.setCdCentroCusto(resultSet.getInt("CODIGO"));
				centroCusto.setNmCentroCusto(resultSet.getString("NOME"));
			}

			return centroCusto;
		} catch (Exception e) {
			throw new BusinessErrorException(e);
		} finally {
			BDUtils.closeResources(connection, preparedStatement, null);
		}
	}

	@Override
	public List<CentroCusto> findAll(String cdUsuario) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			StringBuilder sbUpdate = new StringBuilder();
			sbUpdate.append("SELECT CC.CDUSUARIO AS CDUSUARIO, CC.CDCENTROCUSTO AS CODIGO, CC.NMCENTROCUSTO AS NOME FROM DEGGCENTROCUSTO CC ");
			sbUpdate.append("WHERE CC.CDUSUARIO = ? ");

			String update = sbUpdate.toString();
			System.out.println(update);

			connection = BDUtils.getConnection();
			preparedStatement = connection.prepareStatement(update);
			preparedStatement.setString(1, cdUsuario);

			List<CentroCusto> centroCustoList = new ArrayList<CentroCusto>();
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				CentroCusto centroCusto = new CentroCusto();
				centroCusto.setCdUsuario(resultSet.getString("CDUSUARIO"));
				centroCusto.setCdCentroCusto(resultSet.getInt("CODIGO"));
				centroCusto.setNmCentroCusto(resultSet.getString("NOME"));

				centroCustoList.add(centroCusto);
			}

			return centroCustoList;
		} catch (Exception e) {
			throw new BusinessErrorException(e);
		} finally {
			BDUtils.closeResources(connection, preparedStatement, null);
		}
	}

}
